using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Management;

namespace OfficeClip.OpenSource.Utility
{
 public class Db
 {
	private SqlConnectionString _sqlConnStr;
	public SqlConnectionString SqlConnStr
	{
	 get
	 {
		return _sqlConnStr;
	 }
	 set
	 {
		_sqlConnStr = value;
	 }
	}

	private string _connectionString;
	 private OpenSource.Utility.Log log;

	 public Db(SqlConnectionString sqlConnStr, OpenSource.Utility.Log logObj)
	{
	 log = logObj;
	 _sqlConnStr = sqlConnStr;
	 _connectionString = _sqlConnStr.ToString();
	 Utility.Constants.ErrorMessage = string.Empty;
	}

	 public Db(string connStr, OpenSource.Utility.Log logObj)
	{
	 _connectionString = connStr;
	 _sqlConnStr = new SqlConnectionString(connStr);
	 log = logObj;
	 Utility.Constants.ErrorMessage = string.Empty;
	}

	public object GetScalar(string sqlStr)
	{
	 // We need to first find out what the current version is.
	 SqlConnection myConnection = null;
	 object value = null;
	 try
	 {
		myConnection = new SqlConnection(_connectionString);
		myConnection.InfoMessage += new SqlInfoMessageEventHandler(ProgressStatus);
		SqlCommand cmd = new SqlCommand(sqlStr, myConnection);
		myConnection.Open();
		value = cmd.ExecuteScalar();
		myConnection.Close();
	 }
	 catch (SqlException e)
	 {
		Utility.Constants.ErrorMessage = e.Message;
		if (log != null)
		 log.Write(e.LineNumber + OpenSource.Utility.Constants.LineNum, e.Class, e.Number, e.Message);
		OpenSource.Utility.Constants.IsError = true;
	 }
	 catch (Exception e)
	 {
		Utility.Constants.ErrorMessage = e.Message;
		if (log != null)
		 log.Write(Utility.LogState.Error, "Sql Server", e.Message);
		OpenSource.Utility.Constants.IsError = true;
	 }
	 finally
	 {
		myConnection.Close();
	 }
	 return value;
	}

	public void GetLogicalNames(ref string dbName, ref string logName, string fileName)
	{
	 string sqlStr = @"restore filelistonly from disk = '" + fileName + "'";
	 DataSet ds = ExecuteSql(sqlStr);
	 if ((ds != null) && (ds.Tables.Count == 1))
	 {
		dbName = (string)ds.Tables[0].Rows[0]["LogicalName"];
		logName = (string)ds.Tables[0].Rows[1]["LogicalName"];
	 }
	}

	public DataSet ExecuteSql(string sqlStr)
	{
	 SqlConnection myConnection = null;
	 DataSet ds = new DataSet();
	 try
	 {
		myConnection = new SqlConnection(_connectionString);
		myConnection.InfoMessage += new SqlInfoMessageEventHandler(ProgressStatus);
		SqlDataAdapter adapter = new SqlDataAdapter();
		adapter.SelectCommand = new SqlCommand(sqlStr, myConnection);
		adapter.SelectCommand.CommandTimeout = OpenSource.Utility.Constants.SqlCommandTimeout;
		adapter.Fill(ds);
	 }
	 catch (SqlException e)
	 {
		Utility.Constants.ErrorMessage = e.Message;
		if (log != null)
		 log.Write(e.LineNumber + OpenSource.Utility.Constants.LineNum, e.Class, e.Number, e.Message);
		OpenSource.Utility.Constants.IsError = true;
	 }
	 catch (Exception e)
	 {
		Utility.Constants.ErrorMessage = e.Message;
		if (log != null)
		 log.Write(Utility.LogState.Error, "Sql Server", e.Message);
		OpenSource.Utility.Constants.IsError = true;
	 }
	 finally
	 {
		if (myConnection != null)
		 myConnection.Close();
	 }
	 return ds;
	}

	public bool IsDatabasePresent(string databaseName, out string errorMessage)
	{
	 bool retVal = false;
	 errorMessage = string.Empty;
	 OpenSource.Utility.Constants.IsError = false;
	 OpenSource.Utility.Constants.ErrorMessage = string.Empty;
	 DataSet ds = ExecuteSql("sp_databases");
	 if (!OpenSource.Utility.Constants.IsError)
	 {
		foreach (DataRow dr in ds.Tables[0].Rows)
		{
		 if (((string)dr[0]).ToUpper() == databaseName.ToUpper())
		 {
			retVal = true;
			break;
		 }
		}
	 }
	 else
	 {
		errorMessage = OpenSource.Utility.Constants.ErrorMessage;
	 }
	 if (log != null)
		log.Write(Utility.LogState.None, "Function", "IsDbPresent() - " + retVal.ToString());
	 return retVal;
	}

	public bool IsDatabaseEmpty(out string errorMessage)
	{
	 OpenSource.Utility.Constants.IsError = false;
	 OpenSource.Utility.Constants.ErrorMessage = string.Empty;
	 DataSet ds = ExecuteSql(
		"select name from sysobjects where type = 'U'"
		);
	 errorMessage = OpenSource.Utility.Constants.ErrorMessage;
	 bool retVal = (!OpenSource.Utility.Constants.IsError && (ds.Tables[0].Rows.Count == 0));
	 if (log != null)
		log.Write(Utility.LogState.None, "Function", "IsDatabaseEmpty(): " + retVal.ToString());
	 return retVal;
	}

	public bool IsDatabaseOfficeClip(out string errorMessage)
	{
	 OpenSource.Utility.Constants.IsError = false;
	 OpenSource.Utility.Constants.ErrorMessage = string.Empty;
	 DataSet ds = ExecuteSql(
		"select name from sysobjects where type = 'U' and name = 'siteadministration'"
		);
	 errorMessage = OpenSource.Utility.Constants.ErrorMessage;
	 bool retVal = (!OpenSource.Utility.Constants.IsError && (ds.Tables[0].Rows.Count > 0));
	 if (log != null)
		log.Write(Utility.LogState.None, "Function", "IsDatabaseOfficeClip(): " + retVal.ToString());
	 return retVal;
	}

	/// <summary>
	/// Gets the current version of the sql server instance that is connected...
	/// </summary>
	/// <returns></returns>
	public string GetSqlServerInstance(out string errorMessage)
	{
	 OpenSource.Utility.Constants.IsError = false;
	 OpenSource.Utility.Constants.ErrorMessage = string.Empty;
	 string currentVersion = (string)GetScalar(
		"SELECT SERVERPROPERTY('ProductVersion')"
		);
	 errorMessage = OpenSource.Utility.Constants.ErrorMessage;
	 if (log != null)
	 {
		if (string.IsNullOrEmpty(currentVersion))
		 log.Write(Utility.LogState.Error, "Function", "GetSqlServerInstance(): Could not extract the current version of sql server");
		else
		 log.Write(Utility.LogState.None, "Function", "GetSqlServerInstance(): " + currentVersion);
	 }
	 return currentVersion;
	}

	public string GetCurrentVersion(out string errorMessage)
	{
	 OpenSource.Utility.Constants.IsError = false;
	 OpenSource.Utility.Constants.ErrorMessage = string.Empty;
	 string currentVersion = (string)GetScalar(
		"SELECT field_value FROM siteadministration WHERE id = 4"
		);
	 errorMessage = OpenSource.Utility.Constants.ErrorMessage;
	 if (log != null)
	 {
		if (string.IsNullOrEmpty(currentVersion))
		 log.Write(Utility.LogState.Error, "Function", "GetCurrentVersion(): Could not extract the current version");
		else
		 log.Write(Utility.LogState.None, "Function", "GetCurrentVersion(): " + currentVersion);
	 }
	 return currentVersion;
	}

	public void CreateNewDatabase(string dbName, string filePath, out string errorMessage)
	{
	 if (log != null)
		log.WriteFunction("CreateNewDatabase", dbName, filePath, "", "");
	 OpenSource.Utility.Constants.IsError = false;
	 OpenSource.Utility.Constants.ErrorMessage = string.Empty;
	 string sqlStr = string.Format(
		"CREATE DATABASE {0} ON (NAME = {0}_dat, FILENAME='{1}')",
		dbName, filePath
		);
	 if (log != null)
		log.WriteInfo("Database", "Sql Statement: " + sqlStr);
	 ExecuteSql(sqlStr);
	 errorMessage = OpenSource.Utility.Constants.ErrorMessage;
	 if (log != null)
		log.WriteInfo("Returns", OpenSource.Utility.Constants.IsError.ToString());
	}

	public int GetPermissions(out string errorMessage)
	{
	 int retVal = 0;
	 errorMessage = string.Empty;
	 OpenSource.Utility.Constants.IsError = false;
	 OpenSource.Utility.Constants.ErrorMessage = string.Empty;
	 object obj = GetScalar("select PERMISSIONS()");
	 errorMessage = OpenSource.Utility.Constants.ErrorMessage;
	 if (!OpenSource.Utility.Constants.IsError)
	 {
		retVal = Convert.ToInt32(obj);
	 }
	 if (log != null)
		log.Write(Utility.LogState.None, "Function", "GetPermissions(): " + retVal.ToString());
	 return retVal;
	}

	private void RefreshSqlServer()
	{
	 OpenSource.Utility.Process proc = new OfficeClip.OpenSource.Utility.Process(log);
	 proc.Run("net", " stop mssqlserver");
	 proc.Run("net", " start mssqlserver");
	}

    public void BackupDatabase(string fileName, string dbName)
	{
        BackupDatabase(fileName, dbName, true);
    }

    public void BackupDatabase(string fileName, string dbName, bool isWithSingleUser)
	{
	 string sqlStr = (isWithSingleUser) ?
         @"
				ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
				BACKUP DATABASE [{0}] TO DISK = N'{1}' WITH INIT, NOUNLOAD, NAME = N'{0}', NOSKIP, STATS = 10, NOFORMAT;
				ALTER DATABASE [{0}] SET MULTI_USER
		 "
         :
          @"BACKUP DATABASE [{0}] TO DISK = N'{1}' WITH INIT, NOUNLOAD, NAME = N'{0}', NOSKIP, STATS = 10, NOFORMAT;"
         ;
	 sqlStr = string.Format(sqlStr, dbName, fileName);
	 if (log != null)
	 {
		log.WriteInfo("Sql Stmt", sqlStr);
		log.Flush();
	 }
	 ExecuteSql(sqlStr);
	}

	public void RestoreDatabase(string fileName, string dbName, string dbFileName, 
	 string dbLogicalName, string logLogicalName)
	{
        RestoreDatabase(fileName, dbName, dbFileName, dbLogicalName, logLogicalName, true, true, false, false);
    }

     public void RestoreDatabase(string fileName, string dbName, string dbFileName,
      string dbLogicalName, string logLogicalName, bool isWithSingleUser, bool isWithReplace,
         bool isNoRecovery, bool isNoUnload)
     {
         string dbLogFilename = dbFileName.Substring(0, dbFileName.Length - 9) + "_LOG.ldf";
         StringBuilder sb = new StringBuilder();
         if (isWithSingleUser) sb.Append("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;");
         sb.Append(@"RESTORE DATABASE [{0}] FROM DISK = '{1}' ");
         if (isWithReplace)
         {
             sb.Append(@"  WITH REPLACE, MOVE '{4}' TO '{2}', MOVE '{5}' TO '{3}' ");
         }
         else
         {
             sb.Append(@" WITH FILE = 1 ");
         }
         if (isNoRecovery)
         {
             sb.Append(@", NORECOVERY");
         }
         if (isNoUnload)
         {
             sb.Append(@", NOUNLOAD, STATS = 10");
         }
         sb.Append(";");
         if (isWithSingleUser) sb.Append("ALTER DATABASE [{0}] SET MULTI_USER");
         string sqlStr = string.Format(sb.ToString(), dbName, fileName, dbFileName, dbLogFilename,
            dbLogicalName, logLogicalName);
         if (log != null)
         {
             log.WriteInfo("Sql Stmt", sqlStr);
         }
         ExecuteSql(sqlStr);
     }

     public void BackupTransactionLog(string fileName, string dbName, string backupSetName)
     {
         string sqlStr = @"
            BACKUP LOG [{1}] TO  DISK = N'{0}' 
            WITH NOFORMAT, NOINIT,  NAME = N'{2}', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
        ";
         sqlStr = string.Format(sqlStr, fileName, dbName, backupSetName);
         if (log != null)
         {
             log.WriteInfo("Sql Stmt", sqlStr);
         }
         ExecuteSql(sqlStr);
     }

     public void RestoreTransactionLog(string fileName, string dbName, bool isNoRecovery)
     {
         string sqlStr = @"
            RESTORE LOG [{1}] FROM  DISK = N'{0}' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
        ";
         if (isNoRecovery)
         {
             sqlStr = sqlStr + ", NORECOVERY";
         }
         sqlStr = string.Format(sqlStr, fileName, dbName);
         if (log != null)
         {
             log.WriteInfo("Sql Stmt", sqlStr);
         }
         ExecuteSql(sqlStr);
     }

	public void ProgressStatus(object sender, SqlInfoMessageEventArgs e)
	{
	 string str = string.Empty;
	 foreach (SqlError sErr in e.Errors)
	 {
		if (log != null)
		 log.Write(sErr.LineNumber + OpenSource.Utility.Constants.LineNum, sErr.Class, sErr.Number, sErr.Message);
	 }
	}

	public string GetDatabasePhysicalFileName(string dbName)
	{
	 string errorMsg;
	 string version = GetSqlServerInstance(out errorMsg);
	 if (errorMsg != string.Empty)
		return string.Empty;
	 string sqlStmt = (version.IndexOf("8.") != -1) ?
		"SELECT filename FROM sysdatabases WHERE name='" + dbName + "'" :
		"SELECT physical_name FROM sys.master_files WHERE file_id = 1 and database_id = DB_ID('" + dbName + "')";
	 return (string)GetScalar(sqlStmt);
	}

	public void TestLog()
	{
	 log.WritePass("Test", "Log test successful");
	}
 }
}
